package com.testTimeTag;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import com.poi.POITool;

public class Test {
	
	private static String url="jdbc:mysql://localhost:3306/lottery";
	private static String username="root";
	private static String password="admin@pwd";
	public static DateFormat df=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	public static Connection getConn() {
		Connection con = null;
		try {
			con = DriverManager.getConnection(url, username, password);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return con;
	}
	/**
	 * 部门		姓名		登记号码		日期时间	机器号			编号		比对方式		卡号
	 * `dept`,`name`,`recordnum`,`date`,`mechinenum`,  `num`,	`method`,	`cardnum`      
	 * @param month 要计算的月份
	 * @throws SQLException
	 * @throws ParseException 
	 */
	public static void test(int month) throws SQLException, ParseException{
		Connection con=getConn();
		ResultSet rs=con.createStatement().executeQuery("select dept,name,recordnum,date from timetag where 1=1 #and name='宋红杰'# and recordnum=5");
		Map<String, TagRecord> map=new LinkedHashMap<String, TagRecord>();
		
		while(rs!=null && rs.next()){
			String dept=rs.getString("dept");
			String name=rs.getString("name");
			String recordnum=rs.getString("recordnum");
			Date date=df.parse(rs.getString("date"));
			String key=dept+"_"+recordnum+"_"+name;
			if(!map.containsKey(key)){
				map.put(key, new TagRecord(dept,name,recordnum));
			}
			map.get(key).addRecord(date,month);
		}
		//System.out.println(map);
		
		for(String key:map.keySet()){			
			TagRecord tagRecord=map.get(key);
			List<Record12OneDay> record=tagRecord.getRecord();
			if(record.size()>0){
				Record12OneDay record12OneDay=record.get(record.size()-1);
				Calendar c=Calendar.getInstance();
				c.setTime(record12OneDay.getDate());
				if(c.get(Calendar.DAY_OF_MONTH)!=1){
					//有可能是这个月的考勤没有打完，才会有不为null的情况
					int days=c.getActualMaximum(Calendar.DAY_OF_MONTH)-c.get(Calendar.DAY_OF_MONTH);
					if(record12OneDay.getDate2()!=null){
						//上班漏打卡
						tagRecord.addClockOutLost(record12OneDay.getDate());
						if(days>1){
							//无打卡记录
							for(int i=1;i<=days;i++){
								c.add(Calendar.DAY_OF_MONTH, 1);
								tagRecord.addAbsent(c.getTime());
							}
						}
					}else{
						//无打卡记录
						for(int i=1;i<=days+1;i++){
							tagRecord.addAbsent(c.getTime());
							c.add(Calendar.DAY_OF_MONTH, 1);
						}
					}
				}else{
					//月底最后一天下班未打卡
					/*if(record12OneDay.getDate1()==null){
						tagRecord.addClockOutLost(d)
					}*/
				}
			}
			System.out.println(map.get(key));
		}
	}
	/**
	 * 部门		姓名		登记号码		日期时间	机器号			编号		比对方式		卡号
	 * `dept`,`name`,`recordnum`,`date`,`mechinenum`,  `num`,	`method`,	`cardnum`      
	 * @param month 要计算的月份
	 * @throws SQLException
	 * @throws ParseException 
	 * @throws FileNotFoundException 
	 */
	public static void testByPOI(String path,int month) throws SQLException, ParseException, FileNotFoundException{
		//Connection con=getConn();
		//ResultSet rs=con.createStatement().executeQuery("select dept,name,recordnum,date from timetag where 1=1 #and name='宋红杰'# and recordnum=5");
		Map<String, TagRecord> map=new LinkedHashMap<String, TagRecord>();
		InputStream is=new FileInputStream(path);
		Workbook br=POITool.getBookRead(is);
		Sheet sheet=br.getSheetAt(0);
		Iterator<Row> it=sheet.rowIterator();
		Row row=it.next();
		while(it!=null && it.hasNext()){
			row=it.next(); 
			String dept=row.getCell(0).getStringCellValue();
			String name=row.getCell(1).getStringCellValue();
			String recordnum=row.getCell(2).getStringCellValue();
			Date date=df.parse(row.getCell(3).getStringCellValue());
			String key=dept+"_"+recordnum+"_"+name;
			if(!map.containsKey(key)){
				map.put(key, new TagRecord(dept,name,recordnum));
			}
			map.get(key).addRecord(date,month);
		}
		//System.out.println(map);
		
		for(String key:map.keySet()){			
			TagRecord tagRecord=map.get(key);
			List<Record12OneDay> record=tagRecord.getRecord();
			if(record.size()>0){
				Record12OneDay record12OneDay=record.get(record.size()-1);
				Calendar c=Calendar.getInstance();
				c.setTime(record12OneDay.getDate());
				if(c.get(Calendar.DAY_OF_MONTH)!=1){
					//有可能是这个月的考勤没有打完，才会有不为null的情况
					int days=c.getActualMaximum(Calendar.DAY_OF_MONTH)-c.get(Calendar.DAY_OF_MONTH);
					if(record12OneDay.getDate2()!=null){
						//上班漏打卡
						tagRecord.addClockOutLost(record12OneDay.getDate());
						if(days>1){
							//无打卡记录
							for(int i=1;i<=days;i++){
								c.add(Calendar.DAY_OF_MONTH, 1);
								tagRecord.addAbsent(c.getTime());
							}
						}
					}else{
						//无打卡记录
						for(int i=1;i<=days+1;i++){
							tagRecord.addAbsent(c.getTime());
							c.add(Calendar.DAY_OF_MONTH, 1);
						}
					}
				}else{
					//月底最后一天下班未打卡
					/*if(record12OneDay.getDate1()==null){
						tagRecord.addClockOutLost(d)
					}*/
				}
			}
			System.out.println(map.get(key));
		}
	}
	/**
	 * @param args
	 */
	public static void main(String[] args) {
		try {
			//test(9);
			try {
				testByPOI("d:/all.xlsx", 9);
			} catch (FileNotFoundException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		/*	Calendar c=Calendar.getInstance();
			c.set(Calendar.MONTH, 8);
			System.out.println("daymax:"+c.getActualMaximum(Calendar.DAY_OF_MONTH));*/
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ParseException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

}
